﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using SolidQ.CommonLibraries.GestionErrores;

namespace SolidQBCPLibrary
{
    public class SolidQBCPLibrary
    {

        private int _rowsCopied = 0;
        private int BatchSize;
        private int NotifyAfter;
        private SqlBulkCopyOptions _bulkCopyOptions;
        private int TimeOut;
        private bool _forceColumnMapping;
        private string _hintsString;
        private bool _automaticCollationResolution;

        public SolidQBCPLibrary(int batchSize, int notifyAfter)
        {
            BatchSize = batchSize;
            NotifyAfter = notifyAfter;
            TimeOut = 0;
        }

        public SolidQBCPLibrary(int batchSize, int notifyAfter, string hintsString)
        {
            BatchSize = batchSize;
            NotifyAfter = notifyAfter;
            TimeOut = 0;
            this.SetBulkCopyOptions(hintsString);
        }

        /// <summary>
        /// Default constructor
        ///   BatchSize = 4096
        ///   NotifyAfter = 10000
        /// </summary>
        public SolidQBCPLibrary()
        {
            BatchSize = 4096;
            NotifyAfter = 10000;
            TimeOut = 0;
        }

        /// <summary>
        /// Constructor with BatchSize = 4096
        /// </summary>
        /// <param name="notifyAfter"></param>
        public SolidQBCPLibrary(int notifyAfter)
        {
            BatchSize = 4096;
            NotifyAfter = notifyAfter;
            TimeOut = 0;
        }


        public bool ForceColumnMapping
        {
            get { return (_forceColumnMapping); }
            set { _forceColumnMapping = value; }
        }

        public bool AutomaticCollationResolution
        {
            get { return (_automaticCollationResolution); }
            set { _automaticCollationResolution = value; }
        }

        public SqlBulkCopyOptions BulkCopyOptions
        {
            get { return (_bulkCopyOptions); }
        }

        public void SetBulkCopyOptions(string hintsString)
        {
            _hintsString = hintsString;
            foreach (string hint in hintsString.ToString().Split(','))
            {
                switch (hint.ToUpper())
                {
                    case "TABLOCK":
                        _bulkCopyOptions |= SqlBulkCopyOptions.TableLock;
                        break;
                    case "CHECK_CONSTRAINTS":
                        _bulkCopyOptions |= SqlBulkCopyOptions.CheckConstraints;
                        break;
                    case "FIRE_TRIGGERS":
                        _bulkCopyOptions |= SqlBulkCopyOptions.FireTriggers;
                        break;
                    case "KEEP_IDENTITY":
                        _bulkCopyOptions |= SqlBulkCopyOptions.KeepIdentity;
                        break;
                    case "KEEP_NULLS":
                        _bulkCopyOptions |= SqlBulkCopyOptions.KeepNulls;
                        break;
                    case "USEINTERNALTRANSACTIONS":
                        _bulkCopyOptions |= SqlBulkCopyOptions.UseInternalTransaction;
                        break;
                }
            }
        }



        public bool CopyTable(BCPDataFlow dataflow)
        {
            bool error = false;
            DateTime start = DateTime.Now;
            Console.WriteLine(String.Format("Beginning Copy from {0} to {1}....", dataflow.source.TableName, dataflow.destination.TableName));
            Console.WriteLine("  Hints: " + _hintsString);
            Console.WriteLine("  BatchSize: " + BatchSize);

            _forceColumnMapping = dataflow.ForceColumnMapping;

            ///Fist of all, i check for correct column mapping
            ///
            if (_forceColumnMapping && dataflow.source.ColumnNames.Count != dataflow.destination.ColumnNames.Count)
                throw new ArgumentException("ColumNames arguments do not have the same number of columns, and force_column_mapping is set to true in the XML.", "ColumnNames");

            /// Lo primero va a ser ver si hay que filtrar en origen de forma dinámica
            /// 
            if (dataflow.UseDynamicFilter)
            {
                using (SqlConnection destinationConnection = new SqlConnection(dataflow.DestinationConnectionString))
                {
                    destinationConnection.Open();
                    using (SqlCommand sqlCom = new SqlCommand(dataflow.DynamicFilterDefinition, destinationConnection))
                    {
                        string valor;
                        object retorno = sqlCom.ExecuteScalar();


                        /// Esto es una ñapa para no complicarme la lógica. de momento no necesito nada mas complicado para SQL2Cloud
                        /// Ademas, es opcional asignar valor
                        /// 
                        if (retorno is System.DBNull)
                        {
                            switch (dataflow.DynamicFilterDataType.ToLower())
                            {
                                case "int":
                                    valor = "0";
                                    break;
                                case "bit":
                                    valor = "cast( 0 as bit)";
                                    break;
                                case "varchar":
                                case "nvarchar":
                                    valor = "''";
                                    break;
                                case "uniqueidentifier":
                                    valor = "cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier)";
                                    break;
                                default: valor = "'0'";
                                    break;
                            }

                        }
                        else
                        {
                            Int64 tmp;
                            /// Si el valor no es numérico, tengo que ponerle la comilla simple porque será texto o fecha
                            if (Int64.TryParse(retorno.ToString(), out  tmp))
                                valor = retorno.ToString();
                            else
                                valor = "'" + retorno.ToString() + "'";
                        }
                        dataflow.source.Query = dataflow.source.Query.Replace("$dynamic_filter_value$", valor);
                    }
                }
            }

            // using SqlDataReader to copy the rows:
            using (SqlConnection sourceConnection = new SqlConnection(dataflow.SourceConnectionString))
            {
                // the query property builds the query
                string query = this.BuildQueryString(dataflow, _automaticCollationResolution);
                SqlCommand sourceCommand = new SqlCommand(query, sourceConnection);
                sourceConnection.Open();

                using (SqlDataReader dr = sourceCommand.ExecuteReader())
                {
                    using (SqlBulkCopy s = new SqlBulkCopy(dataflow.DestinationConnectionString, this._bulkCopyOptions))
                    {
                        if (this.TimeOut != 0)
                            s.BulkCopyTimeout = this.TimeOut;

                        s.DestinationTableName = dataflow.destination.TableName;
                        s.BatchSize = this.BatchSize;
                        s.NotifyAfter = this.NotifyAfter;
                        s.SqlRowsCopied += new SqlRowsCopiedEventHandler(s_SqlRowsCopied);

                        if (_forceColumnMapping)
                        {
                            for (int i = 0; i < dataflow.source.ColumnNames.Count; i++)
                            {
                                s.ColumnMappings.Add(dataflow.source.ColumnNames[i], dataflow.destination.ColumnNames[i]);

                            }
                        }

                        try
                        {
                            // Write from the source to the destination.
                            s.WriteToServer(dr);
                        }
                        catch (Exception ex)
                        {
                            Console.Error.WriteLine(ex.Message);
                            error = true;
                        }
                        finally
                        {
                            // Close the SqlDataReader. The SqlBulkCopy
                            // object is automatically closed at the end
                            // of the using block.
                            s.Close();
                        }
                    }
                }
            }

            if (!error)
                Console.WriteLine("Copy complete in {0}:{1}:{2}.{3}  seconds.", DateTime.Now.Subtract(start).Hours, DateTime.Now.Subtract(start).Minutes, DateTime.Now.Subtract(start).Seconds, DateTime.Now.Subtract(start).Milliseconds);

            return (error);
        }

        public void s_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
        {
            lock (this)
            {
                _rowsCopied += NotifyAfter;
            }
            Console.WriteLine("{0} rows sent to destination. {1}  rows copied", NotifyAfter, _rowsCopied);
        }

        public void CreateConfigurationFile(string fullPathToFile, BCPDataFlow dataflow)
        {
            string file = Path.Combine(AppDomain.CurrentDomain.SetupInformation.ApplicationBase, @"SolidQ.BCPConfigFiles\MetaSolidQBCPConfig.xml");
            try
            {
                string xmlContents;
                using (StreamReader sr = new StreamReader(file))
                {
                    xmlContents = sr.ReadToEnd();
                }


                string sp = dataflow.sourceCredential.Password;
                string dp = dataflow.destinationCredential.Password;


                /// Source
                /// 
                xmlContents = xmlContents.Replace("$_VARIABLE_SOURCE_INSTANCE_GENERATED_BY_SOLIDQDATACOLLECTOR_$", dataflow.sourceCredential.InstanceName);
                xmlContents = xmlContents.Replace("$_VARIABLE_SOURCE_DATABASE_GENERATED_BY_SOLIDQDATACOLLECTOR_$", dataflow.sourceCredential.DatabaseName);
                xmlContents = xmlContents.Replace("$_VARIABLE_SOURCE_USER_GENERATED_BY_SOLIDQDATACOLLECTOR_$", dataflow.sourceCredential.UserName);
                xmlContents = xmlContents.Replace("$_VARIABLE_SOURCE_PASSWORD_GENERATED_BY_SOLIDQDATACOLLECTOR_$", sp);
                xmlContents = xmlContents.Replace("$_VARIABLE_SOURCE_TABLE_GENERATED_BY_SOLIDQDATACOLLECTOR_$", dataflow.source.TableName);
                xmlContents = xmlContents.Replace("$_VARIABLE_SOURCE_INTEGRATED_SECURITY_GENERATED_BY_SOLIDQDATACOLLECTOR_$", dataflow.sourceCredential.UseIntegratedSecurity.ToString());

                /// Destination
                /// 
                xmlContents = xmlContents.Replace("$_VARIABLE_DESTINATION_INSTANCE_GENERATED_BY_SOLIDQDATACOLLECTOR_$", dataflow.destinationCredential.InstanceName);
                xmlContents = xmlContents.Replace("$_VARIABLE_DESTINATION_DATABASE_GENERATED_BY_SOLIDQDATACOLLECTOR_$", dataflow.destinationCredential.DatabaseName);
                xmlContents = xmlContents.Replace("$_VARIABLE_DESTINATION_USER_GENERATED_BY_SOLIDQDATACOLLECTOR_$", dataflow.destinationCredential.UserName);
                xmlContents = xmlContents.Replace("$_VARIABLE_DESTINATION_PASSWORD_GENERATED_BY_SOLIDQDATACOLLECTOR_$", dp);
                xmlContents = xmlContents.Replace("$_VARIABLE_DESTINATION_TABLE_GENERATED_BY_SOLIDQDATACOLLECTOR_$", dataflow.destination.TableName);

                System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding();
                FileStream fs = new FileStream(fullPathToFile, FileMode.OpenOrCreate);
                fs.Write(encoding.GetBytes(xmlContents), 0, encoding.GetBytes(xmlContents).Length);
                fs.Close();
            }
            catch (SolidQException)
            { throw; }
            catch (Exception e)
            {
                string scriptName = file;
                throw new SolidQException(scriptName, e.Message, e.InnerException);
            }
        }


        public string BuildQueryString(BCPDataFlow dataflow, bool automaticCollationResolution)
        {
            string retorno = "";

            if (dataflow.source.UseQueryAsSource)
                retorno = dataflow.source.Query;
            else
            {
                if (!automaticCollationResolution)
                    retorno = String.Format("select * from {0}", dataflow.source.TableName);
                else
                {
                    retorno = buildQueryStringWithAutomaticCollationResolution(dataflow);
                }
            }

            return (retorno);
        }

        private string buildQueryStringWithAutomaticCollationResolution(BCPDataFlow dataflow)
        {
            try
            {
                string retorno = "";

                string collate = getDestinationCollation(dataflow);

                Console.WriteLine("  Automatic collation resolution detected: " + collate);

                string q = String.Format(@"
                        select 
	                        case 
		                        when collation_name is not null 
			                        then column_name +' collate {0}' 
		                        else column_name
	                        end as column_name	
                        from information_schema.columns
                        where table_schema = '{1}' and table_name = '{2}'", collate, dataflow.source.Schema, dataflow.source.TableWithoutSchema);


                DataTable t = SolidQSqlConnection.T_SQLScriptWorker.GetDataTable(new SqlConnection(dataflow.SourceConnectionString), 15, q);

                string cols = "";
                foreach (DataRow r in t.Rows)
                {
                    cols += r[0].ToString() + ",";
                }
                cols = cols.Substring(0, cols.Length - 1);

                retorno = String.Format("select {0} from {1}", cols, dataflow.source.TableName);

                return (retorno);
            }
            catch (SolidQException)
            { throw; }
            catch (Exception)
            { throw; }

        }

        private string getDestinationCollation(BCPDataFlow dataflow)
        {
            try
            {
                object retorno;
                using (SqlConnection sq = new SqlConnection(dataflow.DestinationConnectionString))
                {
                    string q = "select serverproperty ('Collation')";

                    sq.Open();
                    retorno = SolidQSqlConnection.T_SQLScriptWorker.ExecuteScalar(sq, 30, q);
                }
                return (retorno.ToString());
            }
            catch (SolidQException)
            { throw; }
            catch (Exception)
            { throw; }

        }
    }
}
